<?php
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

namespace LEA\StageBundle\SQL;

use Doctrine\DBAL\Connection;

class Queries {
	private $connection;
	private $session;

    public function __construct(Connection $dbalConnection, $session)  {
        $this->connection = $dbalConnection;
        $this->session = $session ;
    }

function doQueryListTuteursParAnnee($yearRef=null, $session) {
     if ($yearRef==null) $yearRef=$session->get('REF_YEAR');

    $queryString = "select profCle, nom, prenom, mail, bureau from membre where  anneeReference <= ".$yearRef." and obsolete='0' order by nom  ;" ;
     $stmt = $this->connection->query($queryString);

    return $stmt;
}

function doTuteurQueryFormationsSuivies($conn, $tuteurRef, $yearRef=null) {
    if ($yearRef==null) $yearRef=$_SESSION[REF_YEAR];
    $queryString = "select distinct
                    formation.nom, formation.formationCle from
                    membre inner join contrat on membre.profCle=contrat.tuteurRef
                    inner join etudiant on contrat.etudRef=etudiant.etudCle
                    inner join etudiant_groupe on etudiant.etudCle=etudiant_groupe.etudRef
                                                  and contrat.anneeCle=etudiant_groupe.annee
                    inner join groupe on etudiant_groupe.groupeRef=groupe.groupeCle
                    inner join formation on groupe.formationRef=formation.formationCle
                where membre.profCle = '" . $tuteurRef . "' and membre.obsolete='0' and contrat.anneeCle in (".$yearRef.");";

    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());

    return $result;
}

//Modification JTA 10/05/2012
function doQuerySansTuteur($conn, $formation,$yearRef=null) {
    if ($yearRef==null) $yearRef=$_SESSION[REF_YEAR];
    $queryString = "
    select
        contrat.alternanceCle,
        etudiant.nom as nom_etud,
        etudiant.prenom as nom_prenom,
        if (groupeCle not like concat(formationCle,'%'),concat(groupeCle),formation.formationCle) as formation,
        resp1.mail as mail_resp_form,
        resp2.mail as mail_resp_form2,
        referent.nom as referent,
        referent.mail as mail_referent,
        bureau.entrepriseRef as entreprise,
        if (etapeetudtut.missions is null,infoetud.missions,etapeetudtut.missions),
        if (etapeetudtut.service is null,infoetud.service,etapeetudtut.service),
        if (etapeetudtut.client is null,infoetud.client,etapeetudtut.client),
        if (etapeetudtut.environnementTechnique is null,infoetud.environnementTechnique,etapeetudtut.environnementTechnique),
        if (etapeetudtut.motscles is null,infoetud.motscles,etapeetudtut.motscles)

    from
	contrat inner join etudiant on contrat.etudRef=etudiant.etudCle
                    inner join etudiant_groupe on etudiant.etudCle=etudiant_groupe.etudRef and contrat.anneeCle=etudiant_groupe.annee
                    inner join groupe on etudiant_groupe.groupeRef=groupe.groupeCle
                    inner join formation on groupe.formationRef=formation.formationCle
                    inner join bureau on bureau.bureauCle=contrat.bureauRef
         inner join
            membre as resp1 on resp1.profCle=formation.responsableRef inner join
            membre as resp2 on resp2.profCle=formation.responsableRef2 inner join
            referent on contrat.referentRef=referent.referentCle left join
            etapeetudtut on contrat.alternanceCle=etapeetudtut.alternanceRef
            left join
            infoetud on contrat.alternanceCle=infoetud.alternanceRef

        where '" . $formation . "' like concat('%',formationRef,'%') and tuteurRef=''
          and anneeCle in (".$yearRef.");";

    //echo "querying ",$queryString;
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}


//Modification JTA 10/05/2012
function doQueryListEtudiantsParFormation($formation, $yearRef=null) {

    if ($yearRef==null) $yearRef=$this->session->get('REF_YEAR');
    $queryString = "select
        contrat.alternanceCle,
        etudiant.nom as nom,
        etudiant.prenom as prenom,
        if (groupeCle not like concat(formationCle,'%'),concat(groupeCle),formation.formationCle) as formation,
        resp1.mail as mail_resp_form,
        resp2.mail as mail_resp_form2,
        if (bureau.ville='SANS SIEGE','',bureau.ville) as ville,
        if (referent.nom='__sans','', referent.nom) as referent,
        referent.mail as mail_referent,
        bureau.entrepriseRef as entreprise,
        if (etapeetudtut.missions is null,infoetud.missions,etapeetudtut.missions) as mission,
        if (etapeetudtut.service is null,infoetud.service,etapeetudtut.service) as service,
        if (etapeetudtut.client is null,infoetud.client,etapeetudtut.client) as client,
        if (etapeetudtut.environnementTechnique is null,infoetud.environnementTechnique,etapeetudtut.environnementTechnique) as env,
        contrat.etudRef,
        if(length(ifnull(contrat.tuteurRef,''))=0,'aucun',contrat.tuteurRef) as tuteurRef,
        if (etapeetudtut.motscles is null,infoetud.motscles,etapeetudtut.motscles) as motscles,
        membre.nom as nom_resp,
        membre.mail as mail_resp,
        etudiant.mail as mail_etu,
        etudiant.etudCle as cle_etu,
        if(contrat.notifAttribTuteur is null,0,contrat.notifAttribTuteur) as notif,
        etudiant.mailLille1,
        bureau.distance,
        bureau.adresse,
        bureau.codePostal
    from
	contrat inner join etudiant on contrat.etudRef=etudiant.etudCle
                    inner join etudiant_groupe on etudiant.etudCle=etudiant_groupe.etudRef and contrat.anneeCle=etudiant_groupe.annee
                    inner join groupe on etudiant_groupe.groupeRef=groupe.groupeCle
                    inner join formation on groupe.formationRef=formation.formationCle
                inner join
            membre as resp1 on resp1.profCle=formation.responsableRef
            left join
            membre as resp2 on resp2.profCle=formation.responsableRef2
            left join
            referent on contrat.referentRef=referent.referentCle
            left join
            etapeetudtut on contrat.alternanceCle=etapeetudtut.alternanceRef

            left join
            infoetud on contrat.alternanceCle=infoetud.alternanceRef

            left join
            membre on contrat.tuteurRef=membre.profCle
            left join
            bureau on contrat.bureauRef = bureau.bureauCle
        where '" . $formation . "' like concat('%',formationRef,'%')
              and anneeCle in (".$yearRef.") order by etudiant.nom, formationRef;";

    //echo $queryString;
    $stmt = $this->connection->query($queryString);
	$stmt->execute();

    return $stmt;
}


function doQueryTuteursPotentielsParEtudiant($conn, $etud, $yearRef=null) {
    if ($yearRef==null) $yearRef=$_SESSION[REF_YEAR];
    $queryString = "SELECT distinct temp_tuteurs.tuteurRef, membre.nom, membre.prenom
               FROM contrat inner join (
                        temp_tuteurs inner join membre
                            on temp_tuteurs.tuteurRef=membre.profCle)
                    on contrat.alternanceCle=temp_tuteurs.alternanceRef
                WHERE
                    contrat.etudRef='" . $etud . "' and anneeCle in (".$yearRef.")
                GROUP BY temp_tuteurs.tuteurRef, membre.nom, membre.prenom";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}

function doQueryTuteursPotentielsParFormation($conn, $formationRef, $yearRef=null) {
    if ($yearRef==null) $yearRef=$_SESSION[REF_YEAR];
    $queryString = "SELECT distinct membre.nom, membre.prenom,
                etudiant.nom, etudiant.prenom, temp_tuteurs.formationRef
               FROM contrat inner join (
                        temp_tuteurs inner join membre
                            on temp_tuteurs.tuteurRef=membre.profCle)
                    on contrat.alternanceCle=temp_tuteurs.alternanceRef inner join
                       etudiant on contrat.etudRef=etudiant.etudCle
                WHERE
                     '" . $formationRef . "' like concat('%',formationRef,'%') and anneeCle in (".$yearRef.")
                ORDER BY membre.nom, membre.prenom, etudiant.nom, etudiant.prenom;";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}

/* TO CHECK MODIFY */

function doQueryTuteursEtTuteursPotentielsParFormation($conn, $formationRef, $tuteur, $yearRef=null) {
    if ($yearRef==null) $yearRef=$_SESSION[REF_YEAR];
    $queryString = "(select
        contrat.alternanceCle,
        etudiant.nom as nom_etud,
        etudiant.prenom as prenom_etud,
        if (groupeCle not like concat(formationCle,'%'),concat(groupeCle),formation.formationCle) as formation,
        membre.nom,
        membre.mail,
        if(etudiant.mailLille1 is null,
            etudiant.mail,
            concat(etudiant.mailLille1,concat(',',etudiant.mail))
          )
    from
	contrat
            inner join etudiant on contrat.etudRef=etudiant.etudCle
                    inner join etudiant_groupe on etudiant.etudCle=etudiant_groupe.etudRef and contrat.anneeCle=etudiant_groupe.annee
                    inner join groupe on etudiant_groupe.groupeRef=groupe.groupeCle
                    inner join formation on groupe.formationRef=formation.formationCle
   inner join
                    referent on contrat.referentRef=referent.referentCle inner join
                        membre on (contrat.tuteurRef=membre.profCle ) left join
            temp_tuteurs on temp_tuteurs.tuteurRef=membre.profCle
        where '" . $formationRef . "' like concat('%',groupe.formationRef,'%')
            and anneeCle in (".$yearRef.")
            and membre.profCle like '" . $tuteur . "' and membre.obsolete='0' )  UNION ";

    $queryString.="(select contrat.alternanceCle,
        concat('POTENTIEL - ',etudiant.nom) as nom_etud,
        etudiant.prenom as prenom_etud,
        formation.formationCle as formation,
        membre.nom,
        membre.mail,
        if(etudiant.mailLille1 is null,
            etudiant.mail,
            concat(etudiant.mailLille1,concat(',',etudiant.mail))
          )
    from
	contrat
            inner join etudiant on contrat.etudRef=etudiant.etudCle
                    inner join etudiant_groupe on etudiant.etudCle=etudiant_groupe.etudRef and contrat.anneeCle=etudiant_groupe.annee
                    inner join groupe on etudiant_groupe.groupeRef=groupe.groupeCle
                    inner join formation on groupe.formationRef=formation.formationCle
         inner join
                    temp_tuteurs
                    on contrat.alternanceCle=temp_tuteurs.alternanceRef inner join
                       membre on membre.profCle=temp_tuteurs.tuteurRef
                WHERE
                     '" . $formationRef . "' like concat('%',groupe.formationRef,'%')
                     and anneeCle in (".$yearRef.")
                     and membre.obsolete='0' and membre.profCle like '" . $tuteur . "' ) ";
    //order by  t.formation,t.nom_etud,t.prenom_etud";
    //ORDER BY membre.nom, membre.prenom, etudiant.nom, etudiant.prenom;";
    //echo "querying ",str_replace("\n","<br/>",$queryString)."<br>";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}

function doQueryTuteurEtudiant($conn, $etud, $yearRef=null) {
    if ($yearRef==null) $yearRef=$_SESSION[REF_YEAR];
    $queryString = "SELECT contrat.tuteurRef, membre.nom, membre.prenom
               FROM contrat inner join membre
                            on contrat.tuteurRef=membre.profCle
                WHERE
                    contrat.etudRef like '" . $etud . "'
                    and anneeCle in (".$yearRef.")";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}

function doQueryTuteursPotentiels($formation, $alternanceRef, $yearRef=null) {
    if ($yearRef==null) $yearRef=$session->get('REF_YEAR');
    $queryString = "select distinct
        tuteurRef, nom_tuteur, prenom_tuteur
    from
	temp_tuteurs
        where '" . $formation . "' like concat('%',formationRef,'%')
        and alternanceRef like '" . $alternanceRef . "'";
        //..and anneeCle in (".$yearRef.");";
    //echo $queryString;
    $stmt = $this->connection->query($queryString);
	$stmt->execute();

    return $stmt;
}
//Modification JTA le 10/05/2012
function doQueryEtudiantsPotentiels($conn, $formation, $alternanceRef, $yearRef=null) {
    if ($yearRef==null) $yearRef=$_SESSION[REF_YEAR];
    //TODO adapt
    $queryString = "select
        contrat.alternanceCle,
        etudiant.nom as nom_etud,
        etudiant.prenom as prenom_etud,
        if (groupeCle not like concat(formationCle,'%'),concat(groupeCle),formation.formationCle) as formation,
        referent.nom as referent,
        referent.mail as mail_referent,
        bureau.entrepriseRef as entreprise,
        if (etapeetudtut.missions is null,infoetud.missions,etapeetudtut.missions),
        if (etapeetudtut.service is null,infoetud.service,etapeetudtut.service),
        if (etapeetudtut.client is null,infoetud.client,etapeetudtut.client),
        if (etapeetudtut.environnementTechnique is null,infoetud.environnementTechnique,etapeetudtut.environnementTechnique),
        membre.nom,
        membre.mail,
        etudiant.mail,
        if (etapeetudtut.motscles is null,infoetud.motscles,etapeetudtut.motscles)

    from
	contrat inner join etudiant on contrat.etudRef=etudiant.etudCle
                    inner join etudiant_groupe on etudiant.etudCle=etudiant_groupe.etudRef and contrat.anneeCle=etudiant_groupe.annee
                    inner join groupe on etudiant_groupe.groupeRef=groupe.groupeCle
                    inner join formation on groupe.formationRef=formation.formationCle
                    inner join bureau on bureau.bureauCle=contrat.bureauRef
       inner join
            referent on contrat.referentRef=referent.referentCle inner join
            membre on contrat.tuteurRef=membre.profCle left join
            etapeetudtut on contrat.alternanceCle=etapeetudtut.alternanceRef

            left join
            infoetud on contrat.alternanceCle=infoetud.alternanceRef

        where '" . $formation . "' like concat('%',formationRef,'%')
            and anneeCle in (".$yearRef.")
            and tuteurRef like '" . $tuteur . "' order by formation, nom_etud, prenom_etud ;";

    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}

function doQueryListTuteurs() {
    $queryString = "select profCle, nom, prenom, mail from membre where obsolete='0' order by nom ;";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}




function getCountEntreesTuteursPotentiels($conn) {
    $queryString = "select count(*) from temp_tuteurs ;";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    $row = mysql_fetch_row($result);
    return $row[0];
}

function doQueryListAlternancesAvecTuteurEnTableTemp($conn, $yearRef=null) {
    if ($yearRef==null) $yearRef=$_SESSION[REF_YEAR];
    $queryString = "select temp_tuteurs.alternanceRef, etudiant.nom, etudiant.prenom from
                    etudiant inner join
                        (temp_tuteurs inner join contrat
                                on temp_tuteurs.alternanceRef=contrat.alternanceCle)
                        on etudiant.etudCle=contrat.etudRef
                 where contrat.tuteurRef != ''
                      and anneeCle in (".$yearRef.")
                 group by temp_tuteurs.alternanceRef, etudiant.nom, etudiant.prenom;";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}

function doQueryListAlternancesAvecTuteurEnTableTempParFormation($conn, $forms, $yearRef=null) {
    if ($yearRef==null) $yearRef=$_SESSION[REF_YEAR];
    $queryString = "select temp_tuteurs.alternanceRef, etudiant.nom, etudiant.prenom from
                    etudiant inner join
                        (temp_tuteurs inner join contrat
                                on temp_tuteurs.alternanceRef=contrat.alternanceCle)
                        on etudiant.etudCle=contrat.etudRef
                    inner join etudiant_groupe eg on eg.etudRef=etudiant.etudCle and eg.annee=anneeCle
                    inner join groupe g on g.groupeCle=eg.groupeRef
                 where contrat.tuteurRef != ''
                      and g.formationRef in ('".str_replace(",","','",$forms)."')
                      and anneeCle in (".$yearRef.")
                 group by temp_tuteurs.alternanceRef, etudiant.nom, etudiant.prenom;";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}

function doDeleteTuteursPotentielsPourEtudiant($conn, $alternance) {
    $deleteString = "delete from temp_tuteurs where alternanceRef='" . $alternance . "';";
    $result = mysql_query($deleteString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    if (!$result)
        return false;
    return true;
}

function doQueryListEtudiantsParTuteur($conn, $formation, $tuteur, $yearRef=null) {
    if ($yearRef==null) $yearRef=$_SESSION[REF_YEAR];
    /* Ancienne version
     * $queryString="select distinct
      contrat.alternanceCle,
      etudiant.nom as nom_etud,
      etudiant.prenom as prenom_etud,
      formation.formationCle as formation,
      referent.ville,
      referent.nom as referent,
      referent.mail as mail_referent,
      referent.entrepriseRef as entreprise,
      etapeetudtut.missions,
      etapeetudtut.service,
      etapeetudtut.client,
      etapeetudtut.environnementTechnique,
      membre.nom,
      membre.mail,
      etudiant.mail,
      etapeetudtut.motscles,
      temp_tuteurs.tuteurRef
      from
      ((contrat left join temp_tuteurs on contrat.alternanceCle=temp_tuteurs.alternanceRef)
      inner join
      (etudiant inner join
      (groupe inner join
      formation on formationRef=formationCle)
      on groupeRef=groupeCle)
      on etudCle=etudRef) inner join
      referent on contrat.referentRef=referent.referentCle inner join
      membre on ( membre.profCle like '".$tuteur."' and (contrat.tuteurRef=membre.profCle or temp_tuteurs.tuteurRef = membre.profCle)) left join
      etapeetudtut on contrat.alternanceCle=etapeetudtut.alternanceRef
      where '".$formation."' like concat('%',groupe.formationRef,'%')  order by formation, nom_etud, prenom_etud ;";
     */
// Version avec gestion des bureaux
    $queryString = "select
        contrat.alternanceCle,
        etudiant.nom as nom_etud,
        etudiant.prenom as prenom_etud,
        if (groupeCle not like concat(formationCle,'%'),concat(groupeCle),formation.formationCle) as formation,
        if (bureau.ville='SANS SIEGE','',bureau.ville),
        if (referent.nom='__sans','', referent.nom) as referent,
        referent.mail as mail_referent,
        bureau.entrepriseRef as entreprise,
        if (etapeetudtut.missions is null,infoetud.missions,etapeetudtut.missions),
        if (etapeetudtut.service is null,infoetud.service,etapeetudtut.service),
        if (etapeetudtut.client is null,infoetud.client,etapeetudtut.client),
        if (etapeetudtut.environnementTechnique is null,infoetud.environnementTechnique,etapeetudtut.environnementTechnique),

        membre.nom,
        membre.mail,
        if(etudiant.mailLille1 is null,
            etudiant.mail,
            concat(etudiant.mailLille1,concat(',',etudiant.mail))
          ),
        if (etapeetudtut.motscles is null,infoetud.motscles,etapeetudtut.motscles),
        contrat.tuteurRef,
        contrat.notifAttribTuteur
    from
	contrat
            inner join etudiant on contrat.etudRef=etudiant.etudCle
                    inner join etudiant_groupe on etudiant.etudCle=etudiant_groupe.etudRef and contrat.anneeCle=etudiant_groupe.annee
                    inner join groupe on etudiant_groupe.groupeRef=groupe.groupeCle
                    inner join formation on groupe.formationRef=formation.formationCle
           inner join
                    referent on contrat.referentRef=referent.referentCle inner join
                        membre on (contrat.tuteurRef=membre.profCle ) left join
            etapeetudtut on contrat.alternanceCle=etapeetudtut.alternanceRef
                            left join
            infoetud on contrat.alternanceCle=infoetud.alternanceRef

            left join bureau on contrat.bureauRef = bureau.bureauCle
        where '" . $formation . "' like concat('%',groupe.formationRef,'%')
          and membre.profCle like '" . $tuteur . "'
		  and membre.obsolete='0'
          and anneeCle in (".$yearRef.")
        order by formation, nom_etud, prenom_etud ;";

    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}

function doQueryListTuteursLike($conn, $tuteurLike) {
    $queryString = "Select nom, prenom, profCle, mail from membre where profCle like '" . $tuteurLike . "' and obsolete='0' order by nom, prenom;";
    //echo "querying ",$queryString;
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}

function doQueryTuteurReferentEtudiants($conn, $altRefs, $yearRef=null) {
    if ($yearRef==null) $yearRef=$_SESSION[REF_YEAR];
    $queryString = "select
        contrat.alternanceCle,
        etudiant.nom as nom_etud,
        etudiant.prenom as prenom_etud,
        if(etudiant.mailLille1 is null,
            etudiant.mail,
            concat(
              if (length(etudiant.mailLille1)=0,'',etudiant.mailLille1),
              if (length(etudiant.mailLille1)<>0 and length(etudiant.mail)<>0,',',''),
              if (length(etudiant.mail)=0, '',etudiant.mail)
            )
          ) as  mail_etud,
        if (groupeCle not like concat(formationCle,'%'),concat(groupeCle),formation.formationCle) as formation,
        membre.nom as prof,
        membre.prenom,
        membre.mail,
        referent.nom ,
        referent.prenom,
        referent.mail,
        if(resp2.mail is null,resp.mail,concat(resp.mail,concat(',',resp2.mail))),
        if(resp2.mail is null,
            concat (resp.prenom,' ',resp.nom),
            concat (resp.prenom,' ',resp.nom,' et ', resp2.prenom,' ',resp2.nom))

    from
	contrat inner join etudiant on contrat.etudRef=etudiant.etudCle
                    inner join etudiant_groupe on etudiant.etudCle=etudiant_groupe.etudRef and contrat.anneeCle=etudiant_groupe.annee
                    inner join groupe on etudiant_groupe.groupeRef=groupe.groupeCle
                    inner join formation on groupe.formationRef=formation.formationCle
          inner join membre resp on resp.profCle=formation.responsableRef
          left join membre resp2 on resp2.profCle=formation.responsableRef2 inner join
            referent on contrat.referentRef=referent.referentCle inner join
            membre on contrat.tuteurRef=membre.profCle
        where alternanceCle in (" . $altRefs . ")
            and anneeCle in (".$yearRef.")
        order by formation, prof, nom_etud, prenom_etud ;";

    //error_log("querying ".$queryString);
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}

function getTuteurAutreAnnee($conn, $etudID,$yearRef=null) {
    if ($yearRef==null) $yearRef=$_SESSION[REF_YEAR];

    $queryString =
            "(select anneeCle,tuteurRef from contrat altOld inner join etudiant_groupe eg on eg.etudRef=altOld.etudRef and annee=anneeCle
              where eg.etudRef='$etudID' and anneeCle=".($yearRef-1)." and groupeRef not like 'M1%FI%')  union  " .
            "(select anneeCle,tuteurRef from contrat altOld inner join etudiant_groupe eg on eg.etudRef=altOld.etudRef and annee=anneeCle
              where eg.etudRef='$etudID' and anneeCle=".($yearRef-2)." and groupeRef not like 'M1%FI%') ;";

    //error_log("querying : $queryString");

    $tuts=array();
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    if ($result) {
        $row = mysql_fetch_row($result);
    } else {
        $row = null;
    }

    if ($row)
      {
        if ($row[0]==$yearRef-1) {
          $tuts[0]=$row[1];
        } else {$tuts[0]="inconnu";$tuts[1]=$row[1];}
      }
    else
        $tuts[0]="inconnu";

    $row = mysql_fetch_row($result);
    if ($row)
        $tuts[1]=$row[1];

   return $tuts;
}

function doQueryListOPCA() {
    $queryString = " (select '__sans opca__','INCONNUE') union (select opcaCle,nom from opca where opcaCle not like '__sans opca__' order by nom) ";
    $stmt = $this->connection->query($queryString);

    return $stmt;
}

function doQueryListEntr() {
    $queryString = " (select '__sans entreprise__','SANS ENTREPRISE')
      union (select entrepriseCle, concat(nom,' - ',b.ville) from entreprise inner join bureau b on entrepriseRef=entrepriseCle where entrepriseCle not like '__sans%' and b.bureauCle  like '%_siege' order by nom)
    ";
    $stmt = $this->connection->query($queryString);

    return $stmt;
}

function getGroupesArrayPourFormationsLike($conn, $formations='%') {
    $queryString = "select groupeCle from groupe where ('" . $formations . "' like concat('%',formationRef,'%')) or ('" . $formations . "'='%') order by groupeCle;";
    //echo $queryString;
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    $row = mysql_fetch_row($result);
    $groupes = array();
    while ($row) {
        $groupes[] = $row[0];
        $row = mysql_fetch_row($result);
    }
    return $groupes;
}

function getEtudClesListFromAlternanceCle($conn, $alts) {
    $queryString = "select etudRef from contrat where alternanceCle in (" . $alts . ")";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());

    $list = '';
    $row = mysql_fetch_row($result);
    while ($row) {
        $list.=",'" . $row[0] . "'";
        $row = mysql_fetch_row($result);
    }

    if (strlen($list) > 0)
        return substr($list, 1);

    return $list;
}

// Ajout du 26/01/2012 JTA Gestion des bureau

function doQueryListBureau($entreprise_cle) {
    $queryString = "
    (select bureauCle,concat('SIEGE : ',adresse,' - ',ville) as info from bureau where entrepriseRef='$entreprise_cle' and bureauCle like '%_siege')
    union (select bureauCle,concat(adresse,' - ',ville)
                from bureau where entrepriseRef = '$entreprise_cle' and bureauCle not like '%_siege' order by bureauCle)";

    $stmt = $this->connection->query($queryString);

    return $stmt;
}

function doQueryListReferent($entreprise_cle) {
    $queryString = "(select '__sans referent___".strtoupper($entreprise_cle)."','sans referent') union ".
    "(select referentCle,concat(prenom,' ',nom,' - ',mail) from referent ".
    "where entrepriseRef = '$entreprise_cle' and referentCle not like '%sans referent%' order by referentCle)";

    $stmt = $this->connection->query($queryString);

    return $stmt;
}

//Ajout du 20/03/2012 JTA recuperation donnée pour la génération du ordre de mission
function doQueryDonneeOrdreDeMission($conn, $altCle, $yearRef=null) {
    if ($yearRef==null) $yearRef=$_SESSION[REF_YEAR];
    $queryString = "SELECT membre.nom,
                           membre.prenom,
                         bureau.ville,
                         bureau.adresse,
                        bureau.distance,
        etudiant.nom,etudiant.prenom,entreprise.nom,groupe.formationRef
FROM contrat
inner join etudiant on contrat.etudRef=etudiant.etudCle
                    inner join etudiant_groupe on etudiant.etudCle=etudiant_groupe.etudRef and contrat.anneeCle=etudiant_groupe.annee
                    inner join groupe on etudiant_groupe.groupeRef=groupe.groupeCle
                    inner join formation on groupe.formationRef=formation.formationCle
Inner JOIN membre ON contrat.tuteurRef = membre.profCle
Inner JOIN bureau ON contrat.bureauRef = bureau.bureauCle
Inner JOIN entreprise ON bureau.entrepriseRef = entreprise.entrepriseCle
WHERE alternanceCle ='" . $altCle . "' and anneeCle in (".$yearRef.")";
    //echo $queryString;
    $result = mysql_query($queryString, $conn);
    return $result;
}



function doQueryDonneeDistance($conn) {
    $queryString = "select bureauCle,adresse,ville from bureau where distance='-1'";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}


function doQueryInsertDistance($conn,$distance,$bureauCle) {
    $queryString = "update bureau set distance='".$distance."' where bureauCle='".$bureauCle."';";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    //return $result;
}

function doQueryNombreTotalEtudiant($conn,$bureauCle) {
$queryString = "select COUNT(*) from contrat where bureauRef='".$bureauCle."';";
$result = mysql_query($queryString, $conn);
if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
return $result;
}

function doQueryEtudiantParBureau($conn,$bureauCle) {
   // $yearRef=getCurrYear();
    $yearRef=$_SESSION[REF_YEAR];
    $queryString = "select COUNT(*) from contrat where bureauRef='".$bureauCle."' and anneeCle in (".$yearRef.");";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}


function doQueryAvoirBureaux($conn,$entrepriseCle) {
    $queryString = "select bureauCle, adresse, ville, tel from bureau where entrepriseRef='".$entrepriseCle."' and obsolete ='0' ;";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}

function doQueryAvoirBureauxInactifs($conn,$entrepriseCle) {
    $queryString = "select bureauCle, adresse, ville, tel from bureau where entrepriseRef='".$entrepriseCle."' and obsolete ='1' ;";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}

function doQueryAvoirEntreprise($conn) {
    $queryString = "select entrepriseCle, nom from entreprise;";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}

function doQueryAvoirReferents($conn, $entreprise){
    $queryString = "select nom,prenom,tel,mail,fonction, referentCle,bureauRef from referent where entrepriseRef='".$entreprise."';";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}

function doQueryRemoveReferentFromAllAlternances($conn, $referent){
    $queryString = "update contrat set referentRef=NULL where referentRef='".$referent."';";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
}


function doQuerySupprimerReferent($conn, $referentCle){
    $queryString = "select r1.referentCle from referent r1 inner join referent r2
          on r1.entrepriseRef=r2.entrepriseRef
          where r2.referentCle='$referentCle' and r1.referentCle like '__sans referent__%'";
    $result=mysql_query($queryString, $conn);
    //error_log($queryString." - ".mysql_error());
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    $refRow=mysql_fetch_row($result);$sansRefCle=$refRow[0];

    $queryString = "update contrat set referentRef='$sansRefCle' where referentRef='$referentCle'";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());

    $queryString = "update contrat set referentRef2='$sansRefCle' where referentRef2='$referentCle'";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());

    $queryString = "update contrat set regieReferent='$sansRefCle' where regieReferentRef='$referentCle'";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());

    $queryString = "update contrat set regieReferent2='$sansRefCle' where regieReferentRef2='$referentCle'";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());

    $queryString = "delete from referent where referentCle='".$referentCle."';";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
}


function doQuerySupprimerBureau($conn, $bureauCle){
    $queryString = "select b1.bureauCle from bureau b1 inner join bureau b2 on b1.entrepriseRef=b2.entrepriseRef
          where b2.bureauCle='$bureauCle' and b1.bureauCle like '%_siege'";
    $result=mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    $siegeRow=mysql_fetch_row($result);$siegeCle=$siegeRow[0];

    $queryString = "update contrat set bureauRef='$siegeCle' where bureauRef='$bureauCle'";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());

    $queryString = "update contrat set regieBureauRef='$siegeCle' where regieBureauRef='$bureauCle'";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());

    $queryString = "update referent set bureauRef='$siegeCle' where bureauRef='$bureauCle'";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());

    $queryString = "delete from bureau where bureauCle='".$bureauCle."';";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
}




function doQueryDesactiverBureau($conn, $bureauCle){
    $queryString = "select b1.bureauCle from bureau b1 inner join bureau b2 on b1.entrepriseRef=b2.entrepriseRef
          where b2.bureauCle='$bureauCle' and b1.bureauCle like '%_siege'";
    $result=mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    $siegeRow=mysql_fetch_row($result);$siegeCle=$siegeRow[0];

    $queryString = "update contrat set bureauRef='$siegeCle' where bureauRef='$bureauCle'";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());

    $queryString = "update contrat set regieBureauRef='$siegeCle' where regieBureauRef='$bureauCle'";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());

    $queryString = "update referent set bureauRef='$siegeCle' where bureauRef='$bureauCle'";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());

    $queryString = "Update bureau set obsolete='1' where bureauCle ='".$bureauCle."';";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
}






function doQueryAvoirLeReferent($conn, $referent){
    $queryString = "select nom,prenom,tel,mail,fonction from referent where referentCle='".$referent."';";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}
function doQueryUpdateReferent($conn, $referentCle,$tel,$mail,$fonction){
     $queryString = "update referent set tel='".$tel."', mail='".$mail."'
         ,fonction='".$fonction."' where referentCle='".$referentCle."';";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}

function doQueryDesactiverTuteur($conn, $tuteurRef){
    $queryString = "update membre set obsolete='1' where  profCle ='".$tuteurRef."';";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
	return $result;
}

function doQueryActiverTuteur($conn, $tuteurRef){
    $queryString = "update membre set obsolete='0' where  profCle ='".$tuteurRef."';";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
	return $result;
}

function doQueryAvoirLeTuteur($conn, $tuteurRef){
    $queryString = "select profCle, nom,prenom,tel,mail, anneeReference, bureau  from membre where profCle ='".$tuteurRef."';";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}

function doQueryUpdateTuteur($conn, $profCle,$nom,$prenom,$tel, $mail, $anneeReference, $bureau) {
     $queryString = "update membre set nom='".$nom."',prenom='".$prenom."', tel='".$tel."',mail='".$mail."',
      anneeReference='".$anneeReference."' , bureau='".$bureau."' where profCle= '".$profCle."' ;";
    $result = mysql_query($queryString, $conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
   return $result;
}

}
?>
